package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sg.hms.Dao.XianshiApplicationDao;
import com.sg.hms.Model.Applicationer;
import com.sg.hms.Util.JDBCUtil;

public class XianshiApplicationDaoImpl implements XianshiApplicationDao{

	@Override
	public List xianshiApplication(int start, int pageSize) {
		// TODO Auto-generated method stub
		List list = new ArrayList();
		//StringBuffer sql = new StringBuffer("select maId,moneyapplication.createDate,elName,dName,moneyapplication.state,money,aContent from moneyapplication,application,department,employee where application.aId=moneyapplication.aId and application.elId=employee.elId and department.dId=employee.dId limit ?,?");
		StringBuffer sql = new StringBuffer("select maId,moneyapplication.createDate,");
		sql.append(" elName,dName,moneyapplication.state,money,aContent");
		sql.append(" from moneyapplication,application,department,employee");
		sql.append(" where application.aId=moneyapplication.aId");
		sql.append(" and application.elId=employee.elId and department.dId=employee.dId limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Applicationer app = new Applicationer();
				app.setMaId(rs.getInt("maId"));
				app.setCreateDate(rs.getString("moneyapplication.createDate"));
				app.setElName(rs.getString("elName"));
				app.setdName(rs.getString("dName"));
				app.setMoney(rs.getDouble("money"));
				app.setState(rs.getString("moneyapplication.state"));
				app.setaContent(rs.getString("aContent"));
				list.add(app);
			}
 		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int ApplicationCount(int elId) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(elId) scount from moneyapplication");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List xianshiApplication(int start, int pageSize, String elName,String dName) {
		// TODO Auto-generated method stub
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer("select maId,moneyapplication.createDate,");
		sql.append(" elName,dName,moneyapplication.state,money,aContent");
		sql.append(" from moneyapplication,application,department,employee");
		sql.append(" where application.aId=moneyapplication.aId");
		sql.append(" and application.elId=employee.elId and department.dId=employee.dId");
		sql.append(" and (elName like ? or dName like ?)  limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+elName+"%");
			pst.setString(2, "%"+dName+"%");
			pst.setInt(3, start);
			pst.setInt(4, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Applicationer app = new Applicationer();
				app.setMaId(rs.getInt("maId"));
				app.setCreateDate(rs.getString("moneyapplication.createDate"));
				app.setElName(rs.getString("elName"));
				app.setdName(rs.getString("dName"));
				app.setMoney(rs.getDouble("money"));
				app.setState(rs.getString("moneyapplication.state"));
				app.setaContent(rs.getString("aContent"));
				list.add(app);
			}
 		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int ApplicationCount(int elId, String elName,String dName) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(maId) scount");
		sql.append(" from moneyapplication,application,department,employee");
		sql.append(" where application.aId=moneyapplication.aId");
		sql.append(" and application.elId=employee.elId and");
		sql.append(" department.dId=employee.dId and (elName like ? or dName like ?)");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+elName+"%");
			pst.setString(2, "%"+dName+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public String findHouqinzhuguanName() {
		// TODO Auto-generated method stub
		String hqname = "";
		StringBuffer sql = new StringBuffer("select DISTINCT elName from moneyapplication,application,department,employee");
		sql.append(" where application.aId=moneyapplication.aId");
		sql.append(" and moneyapplication.elId=employee.elId and department.dId=employee.dId");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				hqname = rs.getString("elName");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hqname;
	}
	
	@Override
	public List fenleichakan(int start, int pageSize,String states) {
		// TODO Auto-generated method stub
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer("select maId,moneyapplication.createDate,");
		sql.append(" elName,dName,moneyapplication.state,money,aContent");
		sql.append(" from moneyapplication,application,department,employee");
		sql.append(" where application.aId=moneyapplication.aId");
		sql.append(" and application.elId=employee.elId and department.dId=employee.dId");
		sql.append(" and moneyapplication.state=?  limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, states);
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Applicationer app = new Applicationer();
				app.setMaId(rs.getInt("maId"));
				app.setCreateDate(rs.getString("moneyapplication.createDate"));
				app.setElName(rs.getString("elName"));
				app.setdName(rs.getString("dName"));
				app.setMoney(rs.getDouble("money"));
				app.setState(rs.getString("moneyapplication.state"));
				app.setaContent(rs.getString("aContent"));
				list.add(app);
			}
 		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int fenleichakanCount(int elId, String state) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(maId) scount");
		sql.append(" from moneyapplication,application,department,employee");
		sql.append(" where application.aId=moneyapplication.aId");
		sql.append(" and application.elId=employee.elId and");
		sql.append(" department.dId=employee.dId and moneyapplication.state=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, state);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}
